IN Query

The in command is employed to retrieve records by checking for multiple values in the where clause. While Comparison operators such as =, >= allow you to compare with only one value, the in command enables checking for multiple values.

For Example:

select * from employees where job_id in (9, 5, 6);

The above code will retrieve employees records whose job_id is either 9, 5, or 6.

Result:

In the above table, you can observe that all records have a job_id of either 9, 5, or 6. The in command allows us to check for multiple values, and it can also be used with strings.

Ecto query for in

in/2

Expression

in/2 is utilized to fetch records by checking multiple values in where/3.

Example:

HR.Employee
|> where([c], c.job_id in [1, 2, 3])
|> HR.Repo.all()

The above code will fetch HR.Employee records whose job_id is 1, 2, or 3. where([c], c.job_id in [1, 2, 3]) -> Here [c] is the reference variable that is used to refer to HR.Employee. To learn more about the reference variable, refer to reference variable.

Result:

IEx(29)> HR.Employee |> where([c], c.job_id in [1, 2, 3]) |> HR.Repo.all()

[
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 200,
    first_name: "Jennifer",
    last_name: "Whalen",
    email: "jennifer.whalen@sqltutorial.org",
    phone_number: "515.123.4444",
    hire_date: ~D[1987-09-17],
    salary: Decimal.new("4400.00"),
    manager_id: 101,
    job_id: 3,
    department_id: 1
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 205,
    first_name: "Shelley",
    last_name: "Higgins",
    email: "shelley.higgins@sqltutorial.org",
    phone_number: "515.123.8080",
    hire_date: ~D[1994-06-07],
    salary: Decimal.new("12000.00"),
    manager_id: 101,
    job_id: 2,
    department_id: 11
  },
  %HR.Employee{
    __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
    employee_id: 206,
    first_name: "William",
    last_name: "Gietz",
    email: "william.gietz@sqltutorial.org",
    phone_number: "515.123.8181",
    hire_date: ~D[1994-06-07],
    salary: Decimal.new("8300.00"),
    manager_id: 205,
    job_id: 1,
    department_id: 11
  }
]

In the above example, both records contain a job_id of either 1, 2, or 3.

Keywords

HR.Repo.all(from c in HR.Employee, where: c.job_id in [1,2,3])

Here, where is a key. c.job_id in [1,2,3] is a value specifying that only records with a job_id of either 1, 2, or 3 should be returned.

Back to Homepage